/**************************************************  
*   Create the 26 tables in database. 
*  
*   Created: 08-07-11 
*  
*   Revisions:  
*
*	11-7-11
*	Add status to table Question.
*	Change table name Poll_master to Poll_Pollmaster
*
*	26-7-11  Thanks to David's previous SQL scripts
*	Add trigger to generate create time automatically
*		for table PollAdmin and Poll
*	Add trigger to generate submit time automatically 
*		for table Poll_Question
* 
*
*	27-7-11
*	Add attributes :Fname, Lname, Phone, Address, City, state
*			Zip, Country to tables: SysAdmin,
*			PollAdmin, PollCreator, PollMaster.
*	There attributes are optional. But username, password, Email,
*	Accesibility are compulsory.
*
*
*	30-7-11
*	Change structure of database.
*
*	01-8-11
*	Update trigger User_expiredate, this trigger only for PollAdmin (Accessibility=4).
*
*	02-8-11
*	Update table polluser_question, add attribute answer.
*
*
*	10-8-11
*	Update table Question,change primary key to (QuestionID,QCreateTime)
*	Hence the primary key for table: Options, Feedback, Poll_Question, User_Question all add attribute QCreateTime.
*	Add attribute SubmitTime to table PollUser_Question.
*	Add attribute CreateTime to table feedback.
*
*
*	12-8-11
*	Restructured database, constraints, keys.
*	Add tables: Device, Information, ProfileGroup, AdditionalField, etc.
*
*
*
*	14-8-11
*	Update Message table with new entity: sender and receiver.
*	Add participant_list_id attribute to AdditionalField table.
*	Delete table template, and user_template,add entity IsTemplate to table Participant_list table.
*	Delete Profilegroup_Username entity from Information table.
*
*
*	14-9-11
*	Attribute Respond, UsernameorDevice, and DisplayResults are added to the Question table.
*
*	21-9-11
*	Add attribute MaxTime (INT) to Question.
*
*
*	29-9-11
*	Add attribute UsernameordeviceID VARCHAR2(40) into Answer_Option.
*	Add attribute pollid into Answer_option.
*	Add attribute pollversion into Answer_option.
*
*
*	2-10-11
*	Modify the PK, FK of Participantlist Table, and the corresponding constraints.
*
*
*	
***************************************************/


-- If the user is generic user, the value should be 1. If not, the value should assign to 0;

CREATE TABLE PollUser(
	 Username	VARCHAR2(40)	NOT NULL	PRIMARY KEY
	,GenericUser	INT
	,Password	VARCHAR2(40)
	,Email		VarCHAR2(40)
	,Accessibility	INT
	,FName		VARCHAR2(40)
	,LName		VARCHAR2(40)
	,ExpireDate 	DATE
	,CreateTime 	DATE
);

CREATE table User_Device(
  PollUsername  VARCHAR2(40)
 ,DeviceID  VARCHAR2 (40)
 ,constraint pk_user_divice PRIMARY KEY (PollUsername, DeviceID)
 ,constraint fk_user_puname FOREIGN KEY(PollUsername) REFERENCES PollUser(Username) on 
delete cascade
);

create or replace trigger User_CreateTime
 before insert
 on PollUser
 for each row

 begin 
 select sysdate into :new.CreateTime from dual;
 end;
/

create or replace trigger user_expiredate
 before insert
 on PollUser
 for each row WHEN (new.Accessibility=4)
 begin
 select sysdate + 365 into :new.ExpireDate from dual;
 end;
/

CREATE TABLE Poll(
	 ID			INT
	,Version number	default 0
	,Name		VARCHAR2(40)
	,Description	VARCHAR2(256)
	,CreateTime	DATE
	,Latitude	VARCHAR2(40)
	,Longitude	VARCHAR2(40)
	,CreatorName	VARCHAR2(40)
	,CONSTRAINT pk_polls PRIMARY KEY(ID,Version)
	,CONSTRAINT fk_polls FOREIGN KEY(CreatorName) REFERENCES PollUser(Username) 
ON DELETE CASCADE
);
create or replace trigger Poll_createtime
 before insert
 on Poll
 for each row
 begin 
 select sysdate into :new.CreateTime from dual;
 end;
/

CREATE SEQUENCE sequence_poll
START WITH 1;

CREATE OR REPLACE TRIGGER poll_trigger
BEFORE INSERT
ON Poll
FOR EACH ROW
BEGIN
	
	SELECT sequence_poll.NEXTVAL 
	INTO :NEW.Version
FROM DUAL;
END;
/

CREATE SEQUENCE sequence_poll_id
START WITH 1;

CREATE OR REPLACE TRIGGER poll_trigger_id
BEFORE INSERT
ON Poll
FOR EACH ROW
BEGIN
	
	SELECT sequence_poll_id.NEXTVAL 
	INTO :NEW.ID
FROM DUAL;
END;
/

-- ID: Auto increment.
-- Text: The content of this question.
-- CAI: Correct Answer Indicator, can either be 'Y' or 'N'
-- status: the status of question: open or closed. 
-- Types of MultipleChoice question: true/false, yes/no/abstain, opinion scale, demographic, comparative, and ranking.
-- Respond can either be 1 or 0, default value is 0.
-- UsernameorDevice can either be 1 or 0, default value is 0.
-- DisplayResults can either be 1 or 0, default value is 0.
-- For TemplateQuestion attribute: value 0: normal. value 1: T/F. value 2: Y/N. value 3: 5 opinions. value 4: 7 opinions.
-- For TemplateQuestion attribute: value 5: numeric question. value 6: alphanumeric question.
-- Highest and Lowest: PollCreator may designate correct answer for a definable range
-- TemplateQuestion: if this question is from template, the value is 1. If not, 0.
-- NumberOnly: if answer contains only number, the value is 1. If not, 0.

CREATE TABLE Question (
	 QuestionID	INT				
	,QCreateTime	Date				
	,Text		VARCHAR2(200)
	,Type		VARCHAR2(40)
	,Status		INT		DEFAULT 0
	,Font		VARCHAR2(40)
	,Color		VARCHAR2(40)
	,FontSize	INT		
	,ChartStyles	VARCHAR2(40)
	,ChartColor	VARCHAR2(40)
	,Timer		INT
	,MaxTime	INT
	,CurrentTimer INT
	,ResCounter	CHAR(1)
	,CAI		CHAR(1)
	,Correct1	VARCHAR2(40)
	,Correct2	VARCHAR2(40)
	,Correct3	VARCHAR2(40)
	,Correct4	VARCHAR2(40)
	,Correct5	VARCHAR2(40)
	,Highest	number(20,5)
	,Lowest		number(20,5)
	,TemplateQuestion	INT
	,NumberOnly		INT
	,NumberofAnswer	INT
	,Respond INT	DEFAULT 0
	,UsernameorDevice INT	DEFAULT 0
	,DisplayResults INT DEFAULT 0
	,CONSTRAINT pk_questions PRIMARY KEY(QuestionID)	
);
create or replace trigger Question_CreateTime
 before insert
 on Question
 for each row
 begin 
 select sysdate into :new.QCreateTime from dual;
 end;
/

CREATE SEQUENCE sequen_question
START WITH 1;

CREATE OR REPLACE TRIGGER question_trigger
BEFORE INSERT
ON Question
FOR EACH ROW
BEGIN
	SELECT sequen_question.NEXTVAL 
	INTO :NEW.QuestionID
FROM DUAL;
END;
/


CREATE TABLE Options(
	 OptionID	INT
	,QuestionID	INT
	,Text		VARCHAR2(200)
	,CorrectChoice	CHAR(1)
	,Points		int
	,CONSTRAINT pk_option PRIMARY KEY (OptionID,QuestionID)
	,CONSTRAINT fk_options FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID) ON DELETE CASCADE
);

CREATE SEQUENCE seq_option
START WITH 1;

CREATE OR REPLACE TRIGGER options_trigger
BEFORE INSERT
ON Options
FOR EACH ROW
BEGIN
	SELECT seq_option.NEXTVAL 
	INTO :NEW.OptionID
FROM DUAL;
END;
/


-- If the message has not been read by User, the value is 1. <=> 0.

CREATE TABLE Message (
	 MessageID	INT		NOT NULL	PRIMARY KEY
	,Receiver	VARCHAR2(40)
	,CreateTime	DATE
	,Text		VARCHAR2(200)
	,Unread		INT 	DEFAULT 1
	,Sender		VARCHAR2(40)
	,CONSTRAINT fk_pollreceiver FOREIGN KEY(Receiver) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_pollsender FOREIGN KEY(Sender) REFERENCES PollUser(Username) ON DELETE CASCADE
);

create or replace trigger msg_createtime
 before insert
 on Message
 for each row

 begin 
 select sysdate into :new.CreateTime from dual;
 end;
/

CREATE SEQUENCE seq_msg
START WITH 1;

CREATE OR REPLACE TRIGGER msg_trigger
BEFORE INSERT
ON Message
FOR EACH ROW
BEGIN
	SELECT seq_msg.NEXTVAL 
	INTO :NEW.MessageID
FROM DUAL;
END;
/

-- If the message has not been read by user, the value should be 1. <=> 0.
CREATE TABLE Feedback (
	 FeedbackID	INT
	,FCreateTime	DATE
	,Username	VARCHAR(40)
	,QuestionID	INT
	,Text		VARCHAR(200)
	,Unread		INT	DEFAULT 1
	,CONSTRAINT pk_FeedBack PRIMARY KEY(FeedBackID,Username,QuestionID)
	,CONSTRAINT fk_qid FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID) ON DELETE CASCADE
	,CONSTRAINT fk_userid FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
);

create or replace trigger Feedback_CreateTime
 before insert
 on Feedback
 for each row

 begin 
 select sysdate into :new.FCreateTime from dual;
 end;
/

CREATE SEQUENCE seq_feedback
START WITH 1;

CREATE OR REPLACE TRIGGER feedback_trigger
BEFORE INSERT
ON Feedback
FOR EACH ROW
BEGIN
	SELECT seq_feedback.NEXTVAL 
	INTO :NEW.FeedbackID
FROM DUAL;
END;
/


CREATE TABLE ProfileGroup(
	 ProfileGroupName	VARCHAR2(40)
	,CONSTRAINT pk_profilegroup PRIMARY KEY(ProfileGroupName)
);

-- Modified values is either 'Y' or 'N'. If data is modified by master, this new data will be stored in a new row and modified value will be 'Y'.
-- Sector: There are 5 types of sector according to SRS.
-- SubSector: Divide sector into specific vocations.
-- If it is a template, value for IsTemplate is 1. If not, value is 0.
CREATE TABLE ParticipantList(
	IsTemplate	INT
    ,LName		VARCHAR2(40)	
	,MasterUsername	VARCHAR2(40)
	,Username	VARCHAR2(40)
	,Pollid INT
	,PVersion INT
	,CONSTRAINT pk_plist PRIMARY KEY(LName,MasterUsername)
	,CONSTRAINT fk_plma FOREIGN KEY(MasterUsername) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT FK_plist_pollid FOREIGN KEY(Pollid, PVersion) REFERENCES Poll(ID, Version) ON DELETE CASCADE
	,CONSTRAINT fk_username_plist FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
);


CREATE TABLE AdditionalField(
	 AddiFieldName	VARCHAR2(40)
	,LName	Varchar2(40)
	,Username	VARCHAR2(40)
	,CONSTRAINT fk_participlistid FOREIGN KEY(LName,Username) REFERENCES ParticipantList(LName,MasterUsername) ON DELETE CASCADE
	,CONSTRAINT pk_additionalfield PRIMARY KEY(AddiFieldName)
);

CREATE TABLE Information(
	 Username	VARCHAR2(40)
	,AddiFieldName	VARCHAR2(40)
	,LName 	VARCHAR2(40)
	,Value		VARCHAR2(40)
	,CONSTRAINT pk_infor PRIMARY KEY(Username,AddiFieldName,LName)
	,CONSTRAINT fk_addfilename_info FOREIGN KEY(AddiFieldName) REFERENCES AdditionalField(AddiFieldName) ON DELETE CASCADE
	,CONSTRAINT fk_partList_info FOREIGN KEY(LName,Username) REFERENCES ParticipantList(LName,MasterUsername) ON DELETE CASCADE
);

CREATE TABLE Answer(
	 AnswerID	INT
	,QuestionID	INT
	,PollID		INT
	,PollVersion	Number
	,UsernameorDeviceID	VARCHAR2(40)
	,Text		VARCHAR2(40)
	,SubmitTime	DATE
	,CONSTRAINT pk_answers PRIMARY KEY(AnswerID,QuestionID)
	,CONSTRAINT fk_answers FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID) ON DELETE CASCADE
);

create or replace trigger answer_submittime
 before insert
 on Answer	
 for each row
 begin 
 select sysdate into :new.SubmitTime from dual;
 end;
/

CREATE SEQUENCE seq_answer
START WITH 1;

CREATE OR REPLACE TRIGGER answers_trigger
BEFORE INSERT
ON Answer
FOR EACH ROW
BEGIN
	SELECT seq_answer.NEXTVAL 
	INTO :NEW.AnswerID
FROM DUAL;
END;
/

CREATE TABLE Answer_Option(
	 AnswerID	INT
	,OptionID	INT
	,QuestionID	INT
	,UsernameordeviceID	VARCHAR2(40)
	,CONSTRAINT pk_answer_option PRIMARY KEY(AnswerID,OptionID,QuestionID)
	,CONSTRAINT fk_aid_answer_option FOREIGN KEY(AnswerID,QuestionID) REFERENCES Answer(AnswerID,QuestionID) ON DELETE CASCADE
	,CONSTRAINT fk_oid_answer_option FOREIGN KEY(OptionID,QuestionID) REFERENCES Options(OptionID,QuestionID) ON DELETE CASCADE
);

CREATE TABLE PollUser_ProfileGroup (
	 Username		VARCHAR2(40)
	,ProGroupName	VARCHAR2(40)
	,CONSTRAINT pk_user_profilegroup PRIMARY KEY(Username,ProGroupName)
	,CONSTRAINT fk_uid FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_progroupname FOREIGN KEY(ProGroupName) REFERENCES ProfileGroup(ProfileGroupName) ON DELETE CASCADE
);

CREATE TABLE ProfileGroup_PartiList (
	 LName VARCHAR2(40)
	,Username	VARCHAR2(40)
	,ProGroupName	VARCHAR2(40)
	,CONSTRAINT pk_progroup_paList PRIMARY KEY(LName,ProGroupName)
	,CONSTRAINT fk_lid FOREIGN KEY(LName,Username) REFERENCES ParticipantList(LName,MasterUsername) ON DELETE CASCADE
	,CONSTRAINT fk_profilegroup FOREIGN KEY(ProGroupName) REFERENCES ProfileGroup(ProfileGroupName) ON DELETE CASCADE
);

CREATE TABLE Poll_Question (
	 PollID		INT
	,PollVersion	INT
	,QuestionID	INT
	,PollName	VARCHAR2(40)
	,CONSTRAINT pk_poll_qstn PRIMARY KEY(PollID,QuestionID)
	,CONSTRAINT fk_poll_question FOREIGN KEY(PollID,PollVersion) REFERENCES Poll(ID,Version) ON DELETE CASCADE
	,CONSTRAINT fk_poll_qstn FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID) ON DELETE CASCADE
);

CREATE TABLE User_Msg (
	 Username	VARCHAR2(40)
	,MsgID		INT
	,CONSTRAINT pk_polluser_msg PRIMARY KEY(Username,MsgID)
	,CONSTRAINT fk_user_msg FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_user_msgs FOREIGN KEY(MsgID) REFERENCES Message(MessageID) ON DELETE CASCADE
);

CREATE TABLE User_Participantlist (
	Username	VARCHAR2(40)
	,MasterUsername VARCHAR2(40)
	,LName VARCHAR2(40)
	,CONSTRAINT pk_User_PL PRIMARY KEY (Username,MasterUsername,LName)
	,CONSTRAINT fk_User_PL_User FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_User_PL_PL FOREIGN KEY (MasterUsername,LName) REFERENCES Participantlist (MasterUsername,LName) ON DELETE CASCADE
); 

CREATE TABLE User_Question (
	 Username	VARCHAR2(40)
	,QuestionID	INT
	,CONSTRAINT pk_user_question PRIMARY KEY(Username,QuestionID)
	,CONSTRAINT fk_user FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_poll FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID) ON DELETE CASCADE
);

-- This table only store polluser's username whose accessibility = 1, it will not store poll creator's usernma and poll master's username
CREATE TABLE User_Poll (
	 Username	VARCHAR2(40)
	,PollID		INT
	,PollVersion	INT
	,CONSTRAINT pk_user_Polls PRIMARY KEY(Username,PollID)
	,CONSTRAINT fk_user_poll FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk__user_polls FOREIGN KEY(PollID,PollVersion) REFERENCES Poll(ID,Version) ON DELETE CASCADE
);